"""Add petition record and analysis results tables

Revision ID: c62c08df4365
Revises: 
Create Date: 2025-09-11 17:46:32.676617

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = 'c62c08df4365'
down_revision: Union[str, Sequence[str], None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """Upgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('petition_record',
    sa.Column('xh', sa.String(length=100), nullable=False, comment='信访记录id'),
    sa.Column('ccjbz', sa.String(length=100), nullable=True, comment='初重件标志'),
    sa.Column('flnr', sa.String(length=100), nullable=True, comment='分类内容'),
    sa.Column('tsr', sa.String(length=100), nullable=True, comment='姓名'),
    sa.Column('xfnr', sa.String(length=2000), nullable=True, comment='投诉内容'),
    sa.Column('ts_gk', sa.String(length=2000), nullable=True, comment='概况'),
    sa.Column('ts_xzq', sa.String(length=100), nullable=True, comment='问题属地'),
    sa.Column('ts_ds', sa.String(length=100), nullable=True, comment='地市'),
    sa.Column('ts_qxs', sa.String(length=100), nullable=True, comment='区县市'),
    sa.Column('ts_jd', sa.String(length=100), nullable=True, comment='乡镇街道'),
    sa.Column('ts_dz', sa.String(length=500), nullable=True, comment='地址'),
    sa.Column('ts_qxdw', sa.String(length=100), nullable=True, comment='去向单位'),
    sa.Column('ts_dfnr', sa.String(length=2000), nullable=True, comment='答复内容'),
    sa.Column('djsj', sa.DateTime(), nullable=True, comment='登记时间'),
    sa.Column('ts_bz', sa.String(length=1000), nullable=True, comment='备注'),
    sa.Column('data_source_id', sa.Integer(), nullable=True, comment='数据源ID'),
    sa.Column('creator_id', sa.Integer(), nullable=True, comment='创建者ID'),
    sa.Column('id', sa.Integer(), nullable=False, comment='主键ID'),
    sa.Column('created_at', sa.DateTime(), nullable=True, comment='创建时间'),
    sa.Column('updated_at', sa.DateTime(), nullable=True, comment='更新时间'),
    sa.Column('is_deleted', sa.Boolean(), nullable=True, comment='是否删除'),
    sa.Column('is_active', sa.Boolean(), nullable=True, comment='是否激活'),
    sa.ForeignKeyConstraint(['creator_id'], ['user.id'], ),
    sa.ForeignKeyConstraint(['data_source_id'], ['data_source.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_petition_record_id'), 'petition_record', ['id'], unique=False)
    op.create_index(op.f('ix_petition_record_xh'), 'petition_record', ['xh'], unique=True)
    op.create_table('complaint_type_analysis_result',
    sa.Column('petition_record_id', sa.String(length=100), nullable=False, comment='信访记录id'),
    sa.Column('task_id', sa.Integer(), nullable=False, comment='任务编号'),
    sa.Column('xfnr', sa.String(length=2000), nullable=True, comment='信访内容'),
    sa.Column('dzxx_tsdx', sa.String(length=1000), nullable=True, comment='投诉对象'),
    sa.Column('dzxx_tsdz', sa.String(length=1000), nullable=True, comment='投诉地址'),
    sa.Column('dzxx_xzq', sa.String(length=100), nullable=True, comment='地址信息_行政区'),
    sa.Column('dzxx_jd', sa.String(length=100), nullable=True, comment='地址信息_街道'),
    sa.Column('dzxx_lng', sa.String(length=100), nullable=True, comment='地址信息_经度'),
    sa.Column('dzxx_lat', sa.String(length=100), nullable=True, comment='地址信息_纬度'),
    sa.Column('tslx_yj', sa.String(length=100), nullable=True, comment='投诉类型-一级'),
    sa.Column('tslx_rj', sa.String(length=100), nullable=True, comment='投诉类型-二级'),
    sa.Column('tslx_rjyj', sa.String(length=2000), nullable=True, comment='投诉类型-二级判断依据'),
    sa.Column('tslx_sj', sa.String(length=100), nullable=True, comment='投诉类型-三级'),
    sa.Column('tslx_sjyj', sa.String(length=2000), nullable=True, comment='投诉类型-三级判断依据'),
    sa.Column('tslx_rj_zxd', sa.String(length=100), nullable=True, comment='投诉类型-二级-置信度（0%-100%）'),
    sa.Column('tslx_sj_zxd', sa.String(length=100), nullable=True, comment='投诉类型-三级-置信度（0%-100%）'),
    sa.Column('id', sa.Integer(), nullable=False, comment='主键ID'),
    sa.Column('created_at', sa.DateTime(), nullable=True, comment='创建时间'),
    sa.Column('updated_at', sa.DateTime(), nullable=True, comment='更新时间'),
    sa.Column('is_deleted', sa.Boolean(), nullable=True, comment='是否删除'),
    sa.Column('is_active', sa.Boolean(), nullable=True, comment='是否激活'),
    sa.ForeignKeyConstraint(['petition_record_id'], ['petition_record.xh'], ),
    sa.ForeignKeyConstraint(['task_id'], ['analysis_task.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_complaint_type_analysis_result_id'), 'complaint_type_analysis_result', ['id'], unique=False)
    op.create_table('compliance_analysis_result',
    sa.Column('petition_record_id', sa.String(length=100), nullable=False, comment='信访记录id'),
    sa.Column('task_id', sa.Integer(), nullable=False, comment='任务编号'),
    sa.Column('xfnr', sa.String(length=2000), nullable=True, comment='信访内容'),
    sa.Column('dzxx_tsdx', sa.String(length=1000), nullable=True, comment='投诉对象'),
    sa.Column('dzxx_tsdz', sa.String(length=1000), nullable=True, comment='投诉地址'),
    sa.Column('dzxx_xzq', sa.String(length=100), nullable=True, comment='地址信息_行政区'),
    sa.Column('dzxx_jd', sa.String(length=100), nullable=True, comment='地址信息_街道'),
    sa.Column('dzxx_lng', sa.String(length=100), nullable=True, comment='地址信息_经度'),
    sa.Column('dzxx_lat', sa.String(length=100), nullable=True, comment='地址信息_纬度'),
    sa.Column('gffx_dfnr', sa.String(length=2000), nullable=True, comment='答复内容'),
    sa.Column('gffx_zt', sa.String(length=100), nullable=True, comment='是否规范：1：合规、0：不合规'),
    sa.Column('gffx_pdly', sa.String(length=500), nullable=True, comment='判断理由'),
    sa.Column('id', sa.Integer(), nullable=False, comment='主键ID'),
    sa.Column('created_at', sa.DateTime(), nullable=True, comment='创建时间'),
    sa.Column('updated_at', sa.DateTime(), nullable=True, comment='更新时间'),
    sa.Column('is_deleted', sa.Boolean(), nullable=True, comment='是否删除'),
    sa.Column('is_active', sa.Boolean(), nullable=True, comment='是否激活'),
    sa.ForeignKeyConstraint(['petition_record_id'], ['petition_record.xh'], ),
    sa.ForeignKeyConstraint(['task_id'], ['analysis_task.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_compliance_analysis_result_id'), 'compliance_analysis_result', ['id'], unique=False)
    op.create_table('duplicate_analysis_result',
    sa.Column('bh', sa.String(length=100), nullable=True, comment='序号，记录编号，无实际用途'),
    sa.Column('task_id', sa.Integer(), nullable=False, comment='任务编号'),
    sa.Column('cfbh', sa.String(length=100), nullable=True, comment='重复编号（对应的重复信访内容，编号唯一）'),
    sa.Column('xfxh', sa.String(length=100), nullable=True, comment='信访的初件编号（信访的索引项）'),
    sa.Column('id', sa.Integer(), nullable=False, comment='主键ID'),
    sa.Column('created_at', sa.DateTime(), nullable=True, comment='创建时间'),
    sa.Column('updated_at', sa.DateTime(), nullable=True, comment='更新时间'),
    sa.Column('is_deleted', sa.Boolean(), nullable=True, comment='是否删除'),
    sa.Column('is_active', sa.Boolean(), nullable=True, comment='是否激活'),
    sa.ForeignKeyConstraint(['task_id'], ['analysis_task.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_duplicate_analysis_result_id'), 'duplicate_analysis_result', ['id'], unique=False)
    op.create_table('regional_analysis_result',
    sa.Column('petition_record_id', sa.String(length=100), nullable=False, comment='信访记录id'),
    sa.Column('task_id', sa.Integer(), nullable=False, comment='任务编号'),
    sa.Column('ccjbz', sa.String(length=100), nullable=True, comment='初重件标志'),
    sa.Column('tsr', sa.String(length=100), nullable=True, comment='姓名'),
    sa.Column('xfnr', sa.String(length=2000), nullable=True, comment='投诉内容'),
    sa.Column('ts_gk', sa.String(length=2000), nullable=True, comment='概况'),
    sa.Column('ts_xzq', sa.String(length=100), nullable=True, comment='问题属地'),
    sa.Column('ts_qxdw', sa.String(length=100), nullable=True, comment='去向单位'),
    sa.Column('ts_dfnr', sa.String(length=2000), nullable=True, comment='答复内容'),
    sa.Column('ts_bz', sa.String(length=1000), nullable=True, comment='备注'),
    sa.Column('dzxx_tsdx', sa.String(length=1000), nullable=True, comment='投诉对象'),
    sa.Column('dzxx_tsdz', sa.String(length=1000), nullable=True, comment='投诉地址'),
    sa.Column('dzxx_xzq', sa.String(length=100), nullable=True, comment='地址信息_行政区'),
    sa.Column('dzxx_jd', sa.String(length=100), nullable=True, comment='地址信息_街道'),
    sa.Column('dzxx_lng', sa.String(length=100), nullable=True, comment='地址信息_经度'),
    sa.Column('dzxx_lat', sa.String(length=100), nullable=True, comment='地址信息_纬度'),
    sa.Column('cfxs_bh', sa.String(length=500), nullable=True, comment='重复编号'),
    sa.Column('cfxs_gl', sa.String(length=100), nullable=True, comment='概率'),
    sa.Column('id', sa.Integer(), nullable=False, comment='主键ID'),
    sa.Column('created_at', sa.DateTime(), nullable=True, comment='创建时间'),
    sa.Column('updated_at', sa.DateTime(), nullable=True, comment='更新时间'),
    sa.Column('is_deleted', sa.Boolean(), nullable=True, comment='是否删除'),
    sa.Column('is_active', sa.Boolean(), nullable=True, comment='是否激活'),
    sa.ForeignKeyConstraint(['petition_record_id'], ['petition_record.xh'], ),
    sa.ForeignKeyConstraint(['task_id'], ['analysis_task.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_regional_analysis_result_id'), 'regional_analysis_result', ['id'], unique=False)
    op.create_table('sentiment_analysis_result',
    sa.Column('petition_record_id', sa.String(length=100), nullable=False, comment='信访记录id'),
    sa.Column('task_id', sa.Integer(), nullable=False, comment='任务编号'),
    sa.Column('xfnr', sa.String(length=2000), nullable=True, comment='信访内容'),
    sa.Column('dzxx_tsdx', sa.String(length=1000), nullable=True, comment='投诉对象'),
    sa.Column('dzxx_tsdz', sa.String(length=1000), nullable=True, comment='投诉地址'),
    sa.Column('dzxx_xzq', sa.String(length=100), nullable=True, comment='地址信息_行政区'),
    sa.Column('dzxx_jd', sa.String(length=100), nullable=True, comment='地址信息_街道'),
    sa.Column('dzxx_lng', sa.String(length=100), nullable=True, comment='地址信息_经度'),
    sa.Column('dzxx_lat', sa.String(length=100), nullable=True, comment='地址信息_纬度'),
    sa.Column('qgfx_lx', sa.String(length=100), nullable=True, comment='情感类型，重度'),
    sa.Column('qgfx_pdly', sa.String(length=2000), nullable=True, comment='情感类型判断理由'),
    sa.Column('qgfx_zy', sa.String(length=500), nullable=True, comment='摘要'),
    sa.Column('qgfx_gjc', sa.String(length=1000), nullable=True, comment='关键词'),
    sa.Column('id', sa.Integer(), nullable=False, comment='主键ID'),
    sa.Column('created_at', sa.DateTime(), nullable=True, comment='创建时间'),
    sa.Column('updated_at', sa.DateTime(), nullable=True, comment='更新时间'),
    sa.Column('is_deleted', sa.Boolean(), nullable=True, comment='是否删除'),
    sa.Column('is_active', sa.Boolean(), nullable=True, comment='是否激活'),
    sa.ForeignKeyConstraint(['petition_record_id'], ['petition_record.xh'], ),
    sa.ForeignKeyConstraint(['task_id'], ['analysis_task.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_sentiment_analysis_result_id'), 'sentiment_analysis_result', ['id'], unique=False)
    op.add_column('data_source_preview_log', sa.Column('is_deleted', sa.Boolean(), nullable=True, comment='是否删除'))
    op.alter_column('data_source_preview_log', 'id',
               existing_type=sa.INTEGER(),
               nullable=False,
               autoincrement=True)
    op.drop_index(op.f('idx_data_source_preview_log_created_at'), table_name='data_source_preview_log')
    op.drop_index(op.f('idx_data_source_preview_log_data_source_id'), table_name='data_source_preview_log')
    op.drop_index(op.f('idx_data_source_preview_log_status'), table_name='data_source_preview_log')
    op.create_index(op.f('ix_data_source_preview_log_id'), 'data_source_preview_log', ['id'], unique=False)
    op.drop_constraint(None, 'database_config', type_='foreignkey')
    op.create_foreign_key(None, 'database_config', 'data_source', ['data_source_id'], ['id'], ondelete='CASCADE')
    op.drop_constraint(None, 'excel_config', type_='foreignkey')
    op.create_foreign_key(None, 'excel_config', 'data_source', ['data_source_id'], ['id'], ondelete='CASCADE')
    op.drop_index(op.f('idx_user_session_refresh_token'), table_name='user_session')
    op.create_index(op.f('ix_user_session_refresh_token'), 'user_session', ['refresh_token'], unique=True)
    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f('ix_user_session_refresh_token'), table_name='user_session')
    op.create_index(op.f('idx_user_session_refresh_token'), 'user_session', ['refresh_token'], unique=False)
    op.drop_constraint(None, 'excel_config', type_='foreignkey')
    op.create_foreign_key(None, 'excel_config', 'data_source', ['data_source_id'], ['id'])
    op.drop_constraint(None, 'database_config', type_='foreignkey')
    op.create_foreign_key(None, 'database_config', 'data_source', ['data_source_id'], ['id'])
    op.drop_index(op.f('ix_data_source_preview_log_id'), table_name='data_source_preview_log')
    op.create_index(op.f('idx_data_source_preview_log_status'), 'data_source_preview_log', ['status'], unique=False)
    op.create_index(op.f('idx_data_source_preview_log_data_source_id'), 'data_source_preview_log', ['data_source_id'], unique=False)
    op.create_index(op.f('idx_data_source_preview_log_created_at'), 'data_source_preview_log', ['created_at'], unique=False)
    op.alter_column('data_source_preview_log', 'id',
               existing_type=sa.INTEGER(),
               nullable=True,
               autoincrement=True)
    op.drop_column('data_source_preview_log', 'is_deleted')
    op.drop_index(op.f('ix_sentiment_analysis_result_id'), table_name='sentiment_analysis_result')
    op.drop_table('sentiment_analysis_result')
    op.drop_index(op.f('ix_regional_analysis_result_id'), table_name='regional_analysis_result')
    op.drop_table('regional_analysis_result')
    op.drop_index(op.f('ix_duplicate_analysis_result_id'), table_name='duplicate_analysis_result')
    op.drop_table('duplicate_analysis_result')
    op.drop_index(op.f('ix_compliance_analysis_result_id'), table_name='compliance_analysis_result')
    op.drop_table('compliance_analysis_result')
    op.drop_index(op.f('ix_complaint_type_analysis_result_id'), table_name='complaint_type_analysis_result')
    op.drop_table('complaint_type_analysis_result')
    op.drop_index(op.f('ix_petition_record_xh'), table_name='petition_record')
    op.drop_index(op.f('ix_petition_record_id'), table_name='petition_record')
    op.drop_table('petition_record')
    # ### end Alembic commands ###
